package com.baidu.dao;

import com.baidu.entity.AirBean;
import com.baidu.entity.DistrictBean;
import com.baidu.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

/**
 * 创作时间：2019/9/19 9:14
 * 作者：李增强
 */
public class AirDao {
    JDBCUtils ju = new JDBCUtils();
    /**
     * 这种写法，有可能会出现的问题
     * 要是一个service的方法里面，调用两次dao的方法，第一次调用完dao的方法，会把conn关掉，
     * 第二次再调用就会出错，显示conn已关闭，最好的解决方案，每个方法里面，每次获取conn
     */
    Connection conn = null;
    QueryRunner ru = new QueryRunner();
    public List<AirBean> getAirList(String ssql) {
        /**
         * 因为两张表的主键都是id，串了，把b表的id值放到a表中了
         */


        String sql="select a.*,b.name from t_air a left join t_district b on a.districtid=b.id where 1=1 ";
        sql+=ssql;
        conn = ju.getConn();
        try {
           return ru.query(conn, sql, new BeanListHandler<AirBean>(AirBean.class));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ju.closeJDBC(conn);
        }
        return null;
    }

    public List<DistrictBean> getDistrictList() {
        String sql="select * from t_district";
        conn = ju.getConn();
        try {
            return ru.query(conn, sql, new BeanListHandler<DistrictBean>(DistrictBean.class));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ju.closeJDBC(conn);
        }
        return null;
    }

    public int getAirCount(String ssql) {
        String sql="select count(*) from t_air a left join t_district b on a.districtid=b.id where 1=1 ";
        sql+=ssql;
        conn = ju.getConn();
        try {
            return (int)(long)ru.query(conn,sql,new ScalarHandler<Long>());
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ju.closeJDBC(conn);
        }
        return 0;
    }

    public AirBean getAirById(String id) {
        String sql="select * from t_air where id="+id;
        conn = ju.getConn();
        try {
            AirBean ab = ru.query(conn, sql, new BeanHandler<AirBean>(AirBean.class));
            return ab;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ju.closeJDBC(conn);
        }
        return null;
    }

    public boolean deleteAirById(String id) {
        String sql="delete from  t_air where id="+id;
        conn = ju.getConn();
        try {
            return ru.update(conn, sql)==1;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ju.closeJDBC(conn);
        }
        return false;
    }

    public void updateAir(String districtid, String monitortime, String pm10, String pm25, String monitoringstation, Date lastmodifytime, String id) {
        String sql="update t_air set districtid=?,monitortime=?,pm10=?,pm25=?,monitoringstation=?,lastmodifytime=? where id=?";
        conn = ju.getConn();
        try {
            ru.update(conn, sql,districtid,monitortime,pm10,pm25,monitoringstation,lastmodifytime,id);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ju.closeJDBC(conn);
        }
    }
}
